/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.UserBean;

/**
 *
 * @author Jagadeesh
 */
public class DBClass {

    public Connection createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/rbpl", "root", "");
        return connection;
    }
    //we get values from servlet by passing bean object to insertdetails method

    public int insertDetails(UserBean bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("insert into userdetails(`userName`, `nama`,`password`) values(?,?,md5(?))");
            //set values to prepared statement object by getting values from bean object
            pstmt.setString(1, bb.getUserName());
            pstmt.setString(2, bb.getEmail());
            pstmt.setString(3, bb.getPassword()+"rbplsalting#!12314");
            int i = pstmt.executeUpdate();
            System.out.println("testing");
            return i;
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public List getAlldetails() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from userdetails");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setUserName(rs.getString(1));
            ubean.setEmail(rs.getString(2));
            list.add(ubean);

        }
        return list;
    }

    public UserBean getDetails(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from userdetails where userName=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        UserBean ubean = new UserBean();
        while (rs.next()) {

            ubean.setUserName(rs.getString(1));
            ubean.setEmail(rs.getString(2));
        }
        ubean.setAction("update");
        return ubean;
    }

    public void UpdateDetails(UserBean ubean, String name) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("update userdetails set userName=?,nama=?, password=? where userName=?");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1, ubean.getUserName());
        pstmt.setString(2, ubean.getEmail());
        pstmt.setString(3, ubean.getPassword()+"rbplsalting#!12314");
        pstmt.setString(4, name);
        pstmt.executeUpdate();

    }

    public void deleteDetails(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from userdetails where userName=?");
        pstmt.setString(1, uname);
        pstmt.executeUpdate();
    }

    public boolean cekLogin(String username, String password) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select userName, password from userdetails where userName=? AND password=md5(?)");
        pstmt.setString(1, username);
        pstmt.setString(2, password+"rbplsalting#!12314");
        ResultSet rs = pstmt.executeQuery();


        if (rs.next()) {
            return true;

        } else {
            return false;
        }
    }
    public List getPropinsi() throws SQLException, ClassNotFoundException {
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from propinsi");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setPropinsiid(rs.getString(1));
            ubean.setPropinsi(rs.getString(2));
            list.add(ubean);

        }
        return list;
    }

    public List getResult(String nilai) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select DISTINCT * from userdetails,desa where userdetails.desa_id = desa.id and desa.nama='" + nilai + "'");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            UserBean ubean = new UserBean();
            ubean.setUserName(rs.getString(2));
            ubean.setDateOfBirth(rs.getString(3));
            ubean.setEmail(rs.getString(4));
            ubean.setPhoneNo(rs.getString(5));
            ubean.setPropinsi(rs.getString(7));
            ubean.setKabupaten(rs.getString(8));
            ubean.setKecamatan(rs.getString(9));
            ubean.setDesa(rs.getString(10));
            list.add(ubean);

        }
        return list;
    }
}
